Chapter 20: Pandas
From book
Python Programming (Problem solving, Packages and Libraries)
Published by McGraw Hill Education (India) Private limited.
By:
Note the following:-
Markdown and code cells (extension .ipynb) and then downloaded as html. If someone wants to "modify" or "extend' this document, you may ask for the original .ipynb file by sending me an e-mail at:- 999.anuraggupta@gmail.com20.2. Basics of pandas
20.2.1. Series
The following script shows how to create two Series objects s1 and s2 on Jupyter notebook:
This script is available on page 515 of the book
import pandas as pd
s1 = pd.Series([1, 'cat', (2,3)])
s2 = pd.Series(['Ant', 'bat', 'cat'], index = ['A', 'B', 'C'])
print('s1->', s1)
print('s2->', s2)
20.2.2. DataFrame
The signature of the DataFrame() class of pandas module (conventionally named pd) is as follows:
import pandas as pd
?pd.DataFrame
And the output (truncated and modified for readability) is as follows:
Init signature: pd.DataFrame(data=None, index=None, columns=None, dtype=None, copy=False)
Docstring: Two-dimensional size-mutable, potentially heterogeneous tabular data
structure with labeled axes (rows and columns). Arithmetic operations align on both row and column labels. Can be thought of as a dict-like container for Series objects. The primary pandas data structure
Parameters
(a)data : numpy ndarray (structured or homogeneous), dict, or DataFrame. Dict can contain Series, arrays, constants, or list-like objects
(b)index : Index or array-like. Index to use for resulting frame. Will default to np.arange(n) if no indexing information part of input data and no index provided
(c)columns : Index or array-like. Column labels to use for resulting frame. Will default to np.arange(n) if no column labels are provided
(d)dtype : dtype, default None. Data type to force, otherwise infer
(e)copy : boolean, default False. Copy data from inputs. Only affects DataFrame / 2d ndarray input
Table 20.1: Some data about four planets
In the book the following table for 4 planets is given.
| Planet | Mass $10^{24} kg$ | Diameter (km) | Length of day (Hours) | Distance from Sun($ 10^6$ km) |
|---|---|---|---|---|
| Mercury | .330 | 4879 | 4222.6 | 57.9 |
| Venus | 4.87 | 12110 | 2802.0 | 108.2 |
| Earth | 5.97 | 12756 | 24.0 | 149.6 |
| Mars | 0.642 | 6792 | 24.7 | 227.9 |
The code which creates a DataFrame object for this table is as follows:-
This script is available on page 517 of the book
import numpy as np
import pandas as pd
# my_data is a list of lists
# Each inner list represents one row
my_data = [['Mercury', 0.330, 4879, 4222.6, 57.9],
['Venus', 4.87, 12104, 2802.0, 108.2],
['Earth', 5.97, 12756, 24.0, 149.6],
['Mars', 0.642, 6792, 24.7, 227.9]]
df1 = pd.DataFrame(data = my_data)
print(df1)
You can modify the above script as follows:
import numpy as np
import pandas as pd
# my_data is a list of lists
# Each inner list represents one row
my_data = [[0.330, 4879, 4222.6, 57.9],
[4.87, 12104, 2802.0, 108.2],
[5.97, 12756, 24.0, 149.6],
[0.642, 6792, 24.7, 227.9]]
# Give data for index
my_idx = ['Mercury', 'Venus', 'Earth', 'Mars']
# Give data for columns
my_col = ['Mass', 'Dia', 'Day_len', 'Dist_sun']
df1 = pd.DataFrame(data = my_data, index = my_idx, columns = my_col)
print(df1)
You can always select and print a particular row, i.e., index or column of a DataFrame object as follows:
This script is available on page 518 of the book
#print column Mass
print(df1['Mass'])
# Print row Venus
print(df1.loc['Venus'])
You can test for data in a particular column, row wise using Boolean selection. If you add a line of code as follows, you get:
This script is available on page 519 of the book
# Test for a condition on a given column of the DataFrame object
print(df1.Mass > 1)
You can also select rows which meet a particular condition in a given column. Suppose you want only those rows (i.e., those planets) whose Mass > 1, then you can write the code as follows:
# Select only those rows where Mass > 1
print(df1[df1.Mass > 1])
20.2.3. Creating a DataFrame from list or from list of lists:
You can easily create a DataFrame object either from a list or from a list of lists (i.e., a nested list). An example script is as follows:
This script is available on page 519 of the book
import pandas as pd
L1 = ['a', 'b', 'c']
L2 = [['Row1', 'a', 1], ['Row2', 'b', 2], ['Row3', 'c', 3]]
# Use list to create a 1D DataFrame object
df1 = pd.DataFrame(data = L1, index = [1, 2, 3], columns = ['Letters'])
print(df1)
# Use list of lists to create 2D DataFrame object
df2 = pd.DataFrame(data = L2, columns = ['RowNumb', 'char', 'number'])
print(df2)
20.2.4. Using the key: value pair of a Dictionary to create a DataFrame object
You can also create a DataFrame by using a dictionary of equal-length list. The key of the dictionary becomes the column names and the value of the dictionary must be a list and each of the list item becomes an entry in the column. This is shown in the following code:
This script is available on page 520 of the book
import pandas as pd
some_data = {'Name': ['Anil', 'Babita', 'Charu', 'Dimple'],
'Age': [20, 21, 22, 23],
'Sex': ['M', 'F', 'F', 'F']}
my_df = pd.DataFrame(some_data)
print(my_df)
You can always add a new column to the DataFrame. This can be done by using the following format:-
df_object['new_column_name'] = [list_of_data]
So you can add a new column say Marks to the previous DataFrame. The complete code is shown as follows:
This script is available on page 520 of the book
import pandas as pd
some_data = {'Name': ['Anil', 'Babita', 'Charu', 'Dimple'],
'Age': [20, 21, 22, 23],
'Sex': ['M', 'F', 'F', 'F']}
my_df = pd.DataFrame(some_data)
# print(my_df)
my_df['Marks'] = [70, 75, 80, 85]
print(my_df)
You can make any of the columns as index by using the set_index([column_name]) method of the DataFrame. So if you add the following line and then give a print command as shown, then output is as follows:
This script is available on page 521 of the book
my_df = my_df.set_index(['Name'])
print(my_df)
20.2.5. Panel
The signature for pd.Panel() is:
Init signature: pd.Panel(data=None, items=None, major_axis=None, minor_axis=None, copy=False, dtype=None)
Docstring:
Represents wide format panel data, stored as 3-dimensional array
Parameters
----------
data : ndarray (items x major x minor), or dict of DataFrames
items : Index or array-like axis=0
major_axis : Index or array-like axis=1
minor_axis : Index or array-like axis=2
dtype : dtype, default None. Data type to force, otherwise infer
copy : boolean, default False. Copy data from inputs. Only affects DataFrame / 2d ndarray input
In the book there is an example of 3 shops selling goods G1, G2, G3 and G4 over a number of years.
The book gives the following figure:-
Figure 20.1: Panel with three axes, one each for (1) Good types (2) Years and (3) the two shops
The following script uses pandas to create this 3D data:
This script is available on page 522 of the book
import pandas as pd
import numpy as np
wp = pd.Panel(np.random.randint(1, 11, (2,6,4)), items=['shop1', 'shop2'],
major_axis=pd.date_range('1/1/2012', periods=6, freq = 'A'),
minor_axis=['G1', 'G2', 'G3', 'G4'])
print('For shop1->')
print(wp['shop1'])
print('For shop2->')
print(wp['shop2'])
You can also create a Panel as a dictionary of DataFrames as shown in the following code:
import pandas as pd
import numpy as np
my_data = np.random.randint(1, 11, (2,6,4))
my_items = ['shop1', 'shop2']
my_major_axis = pd.date_range('1/1/2012', periods=6, freq = 'A')
my_minor_axis = ['G1', 'G2', 'G3', 'G4']
wp = pd.Panel(data = my_data, items = my_items,
major_axis = my_major_axis,
minor_axis = my_minor_axis)
print(wp['shop1']) # You can get data for shop2 also
20.3. Using pandas for working on files in various formats
20.3.1. Using pandas to open csv files
The pandas library has a method read_csv(). This method can accept a number of parameters. The signature of the method is as follows:
Signature: pd.read_csv(filepath_or_buffer, sep=',', delimiter=None, header='infer', names=None, index_col=None, usecols=None, squeeze=False, prefix=None, mangle_dupe_cols=True, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skipinitialspace=False, skiprows=None, nrows=None, na_values=None, keep_default_na=True, na_filter=True, verbose=False, skip_blank_lines=True, parse_dates=False, infer_datetime_format=False, keep_date_col=False, date_parser=None, dayfirst=False, iterator=False, chunksize=None, compression='infer', thousands=None, decimal=b'.', lineterminator=None, quotechar='"', quoting=0, escapechar=None, comment=None, encoding=None, dialect=None, tupleize_cols=False, error_bad_lines=True, warn_bad_lines=True, skipfooter=0, skip_footer=0, doublequote=True, delim_whitespace=False, as_recarray=False, compact_ints=False, use_unsigned=False, low_memory=True, buffer_lines=None, memory_map=False, float_precision=None)
Docstring: Read CSV (comma-separated) file into DataFrame
Returns:- DataFrame or TextParser
The book lists only the important parameters.
The book uses following data stored in a csv file
Letter, Fruit, Animal, Place
A, Apple, Ant, Amritsar
B, Berry, Bat, Bangalore
C, Cherry, Cat, Californiaimport pandas as pd
myData = pd.read_csv("C:\Temp\letters.csv", encoding = "ISO-8859-1")
print(myData)
20.3.2. Using pandas to read html files
You can use the read_html() method of pandas to get a list of dataframes. Consider the following code:
This script is available on page 525 of the book
import pandas as pd
def getData(url):
try:
dat = pd.read_html(url)
print(dat)
print(type(dat))
except:
print("Something went wrong")
#Valid url
u1 = 'https://nssdc.gsfc.nasa.gov/planetary/factsheet/'
#Invalid url
u2 = 'https://xxxxyyyyzzzz'
getData(u1)
getData(u2)
20.3.3. Reading/Writing to JSON files
In the following script, a JSON object from a DataFrame object is created. For this you may use a to_json(‘path’) method, specifying the path to the file where the json object is to be saved.
The following code shows this:-
This script is available on page 526 of the book
import pandas as pd
import json
# Create a pandas dataframe from the planet data used earlier.
df = pd.DataFrame([[0.330, 4879, 4222.6, 57.9],
[4.87, 12104, 2802.0, 108.2],
[5.97, 12756, 24.0, 149.6],
[0.642, 6792, 24.7, 227.9]],
index = ['Mercury', 'Venus', 'Earth', 'Mars'],
columns = ['Mass', 'Dia', 'Day_len', 'Dist_sun'])
#Save the dataframe as a JSON object to file testJ.json
df.to_json(r'C:\Temp\testJ.json')
# You can open the testJ.json file and load data to a JSON object called jData
with open(r'C:\Temp\testJ.json', 'r') as jFile:
jData = json.load(jFile)
print(jData)
print('Convert json data into key:value pair')
for key, val in jData.items():
print(str(key) + ':' + str(val))
Exercise
This topic is given on page 528 of the book
a. The following script produces a DataFrame object consisting of 5 rows and 4 columns and containing random integers between 0 and 10:
import numpy as np
import pandas as pd
# Create a list of lists with dimension 5 x 4 ie 5 lists each with 4 items
my_data = np.random.randint(1, 11, (5, 4))
# The index ie rows are labelled 'A', 'B', ....
my_index = ['A', 'B', 'C', 'D', 'E']
# The columns are numbered 'col1', 'col2', ....
my_col = ['col1', 'col2', 'col3', 'col4']
df1 = pd.DataFrame(data = my_data, index = my_index, columns = my_col )
print(df1)
Beyond text book
This topic is given on page 528 of the book
1. Using pandas to create a pivot table
(For detailed explanation, see the book)
The book uses the following table to explain how a pivot table can be created from an excel sheet.
| Shop | Item | Sale | |
|---|---|---|---|
| 0 | shop1 | item1 | 90 |
| 1 | shop2 | item2 | 100 |
| 2 | shop3 | item3 | 50 |
| 3 | shop4 | item1 | 80 |
| 4 | shop1 | item2 | 70 |
| 5 | shop2 | item1 | 100 |
| 6 | shop3 | item3 | 200 |
| 7 | shop1 | item1 | 200 |
Suppose you want to know how much worth of each items were sold by each shop? To answer this question, you need to have the data in a format such that each shop forms a row, each item forms a column and the sum of the sale forms the entry. Then the data will look like as follows:
| Item1 | Item2 | Item3 | |
|---|---|---|---|
| shop1 | 290 | 70 | NaN |
| shop2 | 100 | 100 | NaN |
| shop3 | NaN | NaN | 250 |
| shop4 | 80 | NaN | NaN |
pandas provides a method pivot_table() to create a pivot table from a DataFrame object. The signature of the method is:
pandas.pivot_table(data, values=None, index=None, columns=None, aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All')
A script which creates a DataFrame to represent the above table and to create a pivot table is given as follows:
This script is available on page 530 of the book
import numpy as np
import pandas as pd
# Rows of the DataFrame
r1 = ['shop1', 'item1', 90]
r2 = ['shop2', 'item2', 100]
r3 = ['shop3', 'item3', 50]
r4 = ['shop4', 'item1', 80]
r5 = ['shop1', 'item2', 70]
r6 = ['shop2', 'item1', 100]
r7 = ['shop3', 'item3', 200]
r8 = ['shop1', 'item1', 200]
# DataFrame as a list of lists
sale_data = [r1, r2, r3, r4, r5, r6, r7, r8]
sale_df = pd.DataFrame(sale_data, columns = ['shop', 'item', 'sale'])
print(sale_df)
pv = sale_df.pivot_table(index = 'shop', columns = 'item',
values = 'sale', aggfunc = np.sum)
print('pivot table->')
print(pv)
Beyond text book
This topic is given on pages 530- 536 of the book
2. Using pandas to read/write Excel files
For this exercise, an Excel file named test.xlsx has been created. You can open this file with pandas as shown in the following code:
(Use your file path instead)
This script is available on page 531 of the book
import pandas as pd
myData = pd.read_excel(r"C:\Temp\test.xlsx")
print(myData)
For this exercise, a csv file called letters.csv has been created in Notepad++ (you can use some other editor). The screenshot of this file is shown in Figure 20.8 of the book.
Now you can do some manipulations with this csv data to show the capabilities of Excel methods.
The script given below, does the following:
This script is available on page 532 of the book
import pandas as pd
myData = pd.read_csv("C:\Temp\letters.csv", encoding = "ISO-8859-1")
# Convert the column 'letters' into index
myData.set_index('Letter', inplace=True)
#Add a new row to the dataframe. Index of new row is 'D'
myData.loc['D']= ['Dates', 'Duck', 'Delhi']
#Show that the new row has been added to the dataframe
print(myData)
# Create a pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('C:\Temp\Letters_new.xlsx', engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object.
myData.to_excel(writer, sheet_name='SheetA')
# Close the pandas Excel writer and output the Excel file.
writer.save()
To use xlswriter with pandas, you need to import it. The xlswriter module provides a Workbook class and you need to create an instance of this class giving the path to the xlsx file as a parameter to this class. Note that xlswriter cannot be used to open/ read or modify existing Excel xlsx files.
This script is available on page 533 of the book
import pandas as pd
# Create a pandas dataframe from the planet data used earlier.
df = pd.DataFrame([[0.330, 4879, 4222.6, 57.9],
[4.87, 12104, 2802.0, 108.2],
[5.97, 12756, 24.0, 149.6],
[0.642, 6792, 24.7, 227.9]],
index = ['Mercury', 'Venus', 'Earth', 'Mars'],
columns = ['Mass', 'Dia', 'Day_len', 'Dist_sun'])
# Create a pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter(r'C:\Temp\test.xlsx', engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')
# Close the pandas Excel writer and output the Excel file.
writer.save()
You may now use openpyxl. It is a Python library for reading and writing Excel files.
This script is available on page 534 of the book
from openpyxl import Workbook
#Create an instance of Workbook class
wb = Workbook()
# Get the active worksheet
ws1 = wb.active
#Create a new worksheet
ws2 = wb.create_sheet()
# Access cell A1
cA1 = ws1['A1']
#Assign a value to cell A1
cA1.value = 'Hello World'
#Check value of cell A1
print(cA1.value)
#You can get row and column of cell also
print('row->', cA1.row, 'column->', cA1.column)
#Save the workbook
wb.save(r'C:\Temp\test3.xlsx')
The following script creates a Workbook object from the openpyxl module and then uses this Python module to write to the Workbook object and then read it into a DataFrame
This script is available on page 534 of the book
import pandas as pd
from openpyxl import Workbook
#Create an instance of Workbook class
wb = Workbook()
# Get the active worksheet
ws1 = wb.active
#The 2 for loops will put cell address in each cell as its value
for cObj in ws1['A1': 'D5']:
for c in cObj:
c.value = c.coordinate
wb.save(r'C:\Temp\test4.xlsx')
# You can create a dataframe from the contents of a worksheet
df = pd.DataFrame(ws1.values)
#Output of print() confirms dataframe has been created
print(df)
The following script plots the Dist_sun of the planets. This data is in column 5 of the Excel table. Script is as follows:
This script is available on page 536 of the book
import pandas as pd
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.chart import Reference, Series, LineChart
#Load an Excel file from memory
wb = load_workbook(r'C:\Temp\test.xlsx')
ws = wb.active
# Create a reference to 5th column which has distance from sun data
dval = Reference(ws, min_col=5, min_row=1, max_col=5, max_row=5)
s1 = Series(dval,title="Distance from Sun", title_from_data=True)
chart = LineChart()
chart.append(s1)
ws.add_chart(chart, 'A7')
wb.save(r'C:\Temp\test6.xlsx')